﻿
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class ExcelProvider
    ''Public Shared sql As String = "Provider =Microsoft.Jet.OLEDB.4.0;Data Source=D:\tam1.xls ;Extended  Properties ='Excel 8.0; HDR=YES;'"

    Public Shared file_name As String = ""
    Public Shared Function connecttion() As OleDb.OleDbConnection
       
        Dim cn As OleDbConnection = New OleDbConnection()

        Dim sql As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES""", file_name)
        cn.ConnectionString = sql
        Return cn
    End Function
    Public Shared Sub NonQuery(ByVal sql As String)

        Dim cn As OleDb.OleDbConnection = ExcelProvider.connecttion()
        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(sql, cn)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
       
    End Sub

    Public Shared Function DataAdapter(ByVal sql As String)

        Dim cn As OleDb.OleDbConnection = ExcelProvider.connecttion()
        cn.Open()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql, cn)
        Dim dsLop As DataTable = New DataTable()
        da.Fill(dsLop)
        cn.Close()
        Return dsLop

    End Function

    Public Shared Function laybang(ByVal sql As String) As DataTable
        Dim cnn As SqlConnection = New SqlConnection()
        cnn.ConnectionString = "server=BOCAU-PC\SQLEXPRESS ;database=RapChieuPhim; integrated security=true"
        cnn.Open()
        Dim da As SqlDataAdapter = New SqlDataAdapter(sql, cnn)
        Dim dsLop As DataTable = New DataTable()
        da.Fill(dsLop)
        cnn.Close()
        Return dsLop
    End Function


End Class
